import cx_Oracle
import pymysql
import json
import time

class oracle2mysql(object):
    _ociConn = None
    _ociCur = None
    _myConn = None
    _myCur = None
    _conf = {}

    buffer = []
    curTable = {}
    def loadConf(self, path):
        with open(path, "r", encoding='utf-8') as fp:
            self._conf = json.load(fp)

    def __init__(self, conf = None):
        if isinstance(conf, str):
            self.loadConf(conf)
        else:
            self._conf = conf
        # 资源准备
        ociConf = self._conf['oracle']
        mysqlConf = self._conf['mysql'] 
        uri = "%s/%s@%s:%d/%s" % (ociConf['username'], ociConf['password'], ociConf['host'], int(ociConf['port']), ociConf['database'])
        self._ociConn = cx_Oracle.connect(uri)
        self._ociCur = self._ociConn.cursor()
        self._myConn = pymysql.connect(
            host = mysqlConf['host'],
            user = mysqlConf['username'],
            password = mysqlConf['password'],
            database = mysqlConf['database'])
        self._myCur = self._myConn.cursor()

    # 获取数据
    # def get(self, table, field, where=None):
    def select(self, sql):
        self._ociCur.execute(sql)
        return self

    # 插入数据
    # def insert(self, table, field, datas):
    def insert(self, sql):
        if len(self.buffer) == 0:
            return 0
        sql += self.data2sql(self.buffer)
        self.buffer = [] # 清空buffer
        count = self._insert(sql)
        self.logs(count)
        return count

    def _insert(self, sql):
        try:
            num = self._myCur.execute(sql)
        except Exception as e:
            num = 0
        return num
        
    def data2sql(self, data):
        sql = ''
        for one in data:
            tmp = '('
            for item in one:
                tmp += '"%s",' % str(item)
            tmp = tmp[:-1] + ')'
            sql += tmp + ','
        return sql[:-1]

    def migration(self):
        self.select(self.curTable['select']).savePapeline()

    _selctCount = 0
    def savePapeline(self, block = 1000):
        for one in self._ociCur:
            self._selctCount += 1
            if self.filter(one):
                self.buffer.append(one)
            # 数据分块，避免数据量大到撑满内存
            if len(self.buffer) >= block:
                self.insert(self.curTable['insert'])
        self.insert(self.curTable['insert'])
        return self

    def filter(self, one):
        if self.curTable['pkIndex'] == -1:
            return True
        _, toTableName = self.inOutSplit(self.curTable['table'])
        _, toPK = self.inOutSplit(self.curTable['pk'])
        sql = "SELECT %s FROM %s WHERE '%s'='%s'" % (toPK, toTableName, toPK, one[self.curTable['pkIndex']])
        try:
            self._myCur.execute(sql)
            res = self._myCur.fetchone() == None
        except Exception as e:
            print("ERROR: " +sql)
            res = False
        return res

    def inOutSplit(self, s, sep = '|'):
        res = s.split('|')
        return res[0], res[1]

    def goRun(self):
        # TODO: 引入多线程
        for aTable in self._conf['dbMap']:
            self.curTable = aTable
            self.migration()

    def logs(self, num):
        _, toTableName = self.inOutSplit(self.curTable['table'])
        logStr = "[%s] %s:%d" % (time.strftime('%Y-%m-%d %H:%M:%S'), toTableName, num)
        with open(self._conf['logPath'], "a", encoding='utf-8') as fp:
            fp.write(logStr + '\n')
        print(logStr)

if __name__ == '__main__':
    oj = oracle2mysql('./conf.json')
    oj.goRun()
    print(oj._selctCount)